#include "querywidget.h"

QueryWidget::QueryWidget( QString collection_table_arg,
                          int number_of_collections,
                          QString last_exp_save_dir )
{
this->setAttribute(Qt::WA_DeleteOnClose);
setupUi(this);
readSettings();

    if ( true == last_exp_save_dir.isEmpty() )
    {
    last_path = QDir::homePath();
    }
    else
    {
    QDir test_dir(last_exp_save_dir);
        if ( true == test_dir.isReadable() )
        {
        last_path = last_exp_save_dir;
        }
        else
        {
        last_path = QDir::homePath();
        }
    }

    if ( true == collection_table_arg.isEmpty() )
    {
    collection_table = QLatin1String("a001");
    }
    else
    {
    collection_table = collection_table_arg;
    }

queryTextEdit = new TextEdit(expressionGroupBox);
queryTextEdit->setObjectName(QString::fromUtf8("queryTextEdit"));
queryTextEdit->setAcceptRichText(false);
queryTextEdit->setPlainText(last_query);

// caution! check the layout name in ui_querywidget.h file!
vboxLayout4->addWidget(queryTextEdit);

Schema * schema = new Schema();

QMap<int, QString> column_names = schema->column_names();
    for ( int i = 0; i < column_names.size(); ++i )
    {
    column_names_list << column_names.value(i);
    }

    for (int i = 1; i <= number_of_collections; i++)
    {
    const QChar fill(QLatin1Char('0'));
    QString table_name = QLatin1String("a");
    QString formatted_number = QString("%1").arg(i, 3, 10, fill);
    table_name.append(formatted_number);
    column_names_list << table_name;
    }

QCompleter * completer = new QCompleter(column_names_list, this);

queryTextEdit->setCompleter(completer);

delete schema;

q1 = QLatin1String("SELECT denomination, obj_year, marks,mint, obj_type, obj_undertype\nFROM ");
q1.append(collection_table_arg);
q1.append( QLatin1String("\nORDER BY obj_year,denomination") );

q2 = QLatin1String("SELECT DISTINCT(denomination)\nFROM ");
q2.append(collection_table_arg);

q3 = QLatin1String("SELECT SUM(act_value), act_curr\nFROM ");
q3.append(collection_table_arg);
q3.append( QLatin1String("\nGROUP BY act_curr") );

q4 = QLatin1String("SELECT COUNT(1)\nFROM ");
q4.append(collection_table_arg);
q4.append( QLatin1String("\nWHERE status != 'sold' OR status IS NULL") );

q5 = QLatin1String("SELECT obj_type, COUNT(1), MAX(act_value), MIN(act_value), SUM(act_value)\nFROM ");
q5.append(collection_table_arg);
q5.append("\nWHERE obj_type IS NOT NULL AND obj_year >= 1700 AND obj_year <= 2008");
q5.append( QLatin1String("\nGROUP BY obj_type") );

QObject::connect( queryToolButton,
                  SIGNAL( clicked() ),
                  this,
                  SLOT( exec_query() ) );
QObject::connect( toolButton_1,
                  SIGNAL( clicked() ),
                  this,
                  SLOT( show_q1() ) );
QObject::connect( toolButton_2,
                  SIGNAL( clicked() ),
                  this,
                  SLOT( show_q2() ) );
QObject::connect( toolButton_3,
                  SIGNAL( clicked() ),
                  this,
                  SLOT( show_q3() ) );
QObject::connect( toolButton_4,
                  SIGNAL( clicked() ),
                  this,
                  SLOT( show_q4() ) );
QObject::connect( toolButton_5,
                  SIGNAL( clicked() ),
                  this,
                  SLOT( show_q5() ) );
QObject::connect( exportToolButton,
                  SIGNAL( clicked() ),
                  this,
                  SLOT( export_xml() ) );
                  
}


QueryWidget::~QueryWidget()
{
}

void QueryWidget::writeSettings()
{
QSettings settings( QSettings::IniFormat,
                    QSettings::UserScope,
                    QApplication::organizationName(),
                    QApplication::applicationName() );
settings.setFallbacksEnabled(false);
settings.beginGroup( QLatin1String("QueryWidget") );
settings.setValue( QLatin1String("size"), QVariant( this->size() ) );
settings.setValue( QLatin1String("pos"), QVariant( this->pos() ) );
settings.setValue( QLatin1String("last_query"), QVariant( queryTextEdit->toPlainText() ) );
settings.endGroup();
}

void QueryWidget::readSettings()
{
QSettings settings( QSettings::IniFormat,
                    QSettings::UserScope,
                    QApplication::organizationName(),
                    QApplication::applicationName() );
settings.setFallbacksEnabled(false);
settings.beginGroup( QLatin1String("QueryWidget") );
resize( settings.value( QLatin1String("size"), QSize( 700, 300 ) ).toSize() );
move( settings.value( QLatin1String("pos"), QPoint( 0, 0 ) ).toPoint() );
last_query = settings.value( QLatin1String("last_query"), QLatin1String("") ).toString();
settings.endGroup();
}

void QueryWidget::delete_model()
{
QSqlQueryModel * model = qobject_cast<QSqlQueryModel *>( queryTableView->model() );
    if (NULL != model)
    {
    model->clear();
    }
queryTableView->setModel( new QSqlQueryModel() );
delete model;
model = NULL;
}

void QueryWidget::closeEvent( QCloseEvent * e )
{
writeSettings();
delete_model();
e->accept();
}

void QueryWidget::exec_query()
{
QSqlDatabase db = QSqlDatabase::database("collection", false);
    if ( !db.isOpen() )
    {
    QMessageBox::warning( this,
                          tr("Error"),
                          tr("Database is closed.") );
    }

QString query_source = queryTextEdit->toPlainText();
    if ( query_source.isEmpty() )
    {
    return;
    }

QSqlQueryModel * old_model = qobject_cast<QSqlQueryModel *>( queryTableView->model() );

qApp->processEvents();

QRegExp rx0( QLatin1String("\\bDROP\\b"),     Qt::CaseInsensitive );
QRegExp rx1( QLatin1String("\\bCREATE\\b"),   Qt::CaseInsensitive );
QRegExp rx2( QLatin1String("\\bDELETE\\b"),   Qt::CaseInsensitive );
QRegExp rx3( QLatin1String("\\bTRUNCATE\\b"), Qt::CaseInsensitive );
QRegExp rx4( QLatin1String("\\bINSERT\\b"),   Qt::CaseInsensitive );
    if ( query_source.contains(rx0) ||
         query_source.contains(rx1) ||
         query_source.contains(rx2) ||
         query_source.contains(rx3) ||
         query_source.contains(rx4) )
    {
    QMessageBox::critical( this,
                           tr("Error"),
                           tr("Query contains CREATE, DROP, DELETE, INSERT or TRUNCATE keyword.\nUse iSQL or Flamerobin for other tasks as SELECT or UPDATE query.") );
    return;
    }

QSqlQueryModel * model = new QSqlQueryModel(queryTableView);
QSqlQuery q = QSqlQuery(query_source, db);

    if ( true == q.isSelect() )
    {
    model->setQuery(q);
        if ( model->lastError().type() != QSqlError::NoError )
        {
        QMessageBox::warning( this,
                              tr("Database message"),
                              model->lastError().text() );
        }
    qApp->processEvents();
    queryTableView->setModel(model);
        if ( NULL != old_model )
        {
        delete old_model;
        old_model = NULL;
        }
    tabWidget->setCurrentIndex(1);
    qApp->processEvents();
    }
    else
    {
        if ( q.lastError().type() != QSqlError::NoError )
        {
        QMessageBox::warning( this,
                              tr("Database message"),
                              q.lastError().text() );
        }
        else
        {
        QMessageBox::information( this,
                                  tr("Information"),
                                  tr("Done.") );
        }
    queryTableView->setModel(model);
    q.clear();
    qApp->processEvents();
        if ( NULL != old_model )
        {
        delete old_model;
        old_model = NULL;
        }
    }

}

void QueryWidget::show_q1()
{
queryTextEdit->clear();
queryTextEdit->setPlainText(q1);
}

void QueryWidget::show_q2()
{
queryTextEdit->clear();
queryTextEdit->setPlainText(q2);
}

void QueryWidget::show_q3()
{
queryTextEdit->clear();
queryTextEdit->setPlainText(q3);
}

void QueryWidget::show_q4()
{
queryTextEdit->clear();
queryTextEdit->setPlainText(q4);
}

void QueryWidget::show_q5()
{
queryTextEdit->clear();
queryTextEdit->setPlainText(q5);
}

void QueryWidget::export_xml()
{
qApp->processEvents();

QSqlQueryModel * model = qobject_cast<QSqlQueryModel *>( queryTableView->model() );
    if ( NULL == model )
    {
    return;
    }

    if ( true != model->hasIndex(0,0) )
    {
    return;
    }

QSqlRecord record = model->record(0);
int number_of_columns = model->columnCount();

QMap<int, QStringList> m;
    for ( int i = 0; i < number_of_columns; ++i )
    {
    QSqlField field = record.field(i);
    QStringList l;
    l << QString( field.name() );
    l << QString( QVariant::typeToName( field.type() ) );
    m[i] = l;
    }

QString header_data = QLatin1String("<?xml version=\"1.0\"?>\n");
header_data.append( QLatin1String("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\n") );
header_data.append( QLatin1String("xmlns:o=\"urn:schemas-microsoft-com:office:office\"\n") );
header_data.append( QLatin1String("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n") );
header_data.append( QLatin1String("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\n") );
header_data.append( QLatin1String("xmlns:html=\"http://www.w3.org/TR/REC-html40\"\n") );
header_data.append( QLatin1String("xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"\n") );
header_data.append( QLatin1String("xmlns:x2=\"http://schemas.microsoft.com/office/excel/2003/xml\">\n") );
header_data.append( QLatin1String("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">\n") );
header_data.append( QLatin1String("</DocumentProperties>\n") );
header_data.append( QLatin1String("<OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">\n") );
header_data.append( QLatin1String("</OfficeDocumentSettings>\n") );
header_data.append( QLatin1String("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\n") );
header_data.append( QLatin1String("<ProtectStructure>False</ProtectStructure>\n") );
header_data.append( QLatin1String("<ProtectWindows>False</ProtectWindows>\n") );
header_data.append( QLatin1String("</ExcelWorkbook>\n") );
header_data.append( QLatin1String("<Styles>\n") );
header_data.append( QLatin1String("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\n") );
header_data.append( QLatin1String("<Alignment ss:Vertical=\"Bottom\"/>\n") );
header_data.append( QLatin1String("<Borders/>\n") );
header_data.append( QLatin1String("<Font/>\n") );
header_data.append( QLatin1String("<Interior/>\n") );
header_data.append( QLatin1String("<NumberFormat/>\n") );
header_data.append( QLatin1String("<Protection/>\n") );
header_data.append( QLatin1String("</Style>\n") );
header_data.append( QLatin1String("<Style ss:ID=\"s21\">\n") );
header_data.append( QLatin1String("<Font ss:Color=\"#FFFFFF\"/>\n") );
header_data.append( QLatin1String("<Interior ss:Color=\"#333399\" ss:Pattern=\"Solid\"/>\n") );
header_data.append( QLatin1String("</Style>\n") );
header_data.append( QLatin1String("</Styles>\n") );
header_data.append( QLatin1String("<Worksheet ss:Name=\"") );
header_data.append( QLatin1String("query_") );
header_data.append(collection_table);
header_data.append( QLatin1String("\">\n") );
header_data.append( QLatin1String("<Table>\n") );
header_data.append( QLatin1String("<Column ss:Index=\"1\" ss:Width=\"80\"/>\n") );
header_data.append( QLatin1String("<Column ss:Index=\"") );
header_data.append( QString::number(number_of_columns) );
header_data.append( QLatin1String("\" ss:Width=\"120\"/>\n") );
header_data.append( QLatin1String("<Row>\n") );
    for ( int i = 0; i < number_of_columns; ++i )
    {
    header_data.append( QLatin1String("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">") );
    QVariant v = model->headerData(i, Qt::Horizontal, Qt::DisplayRole );
    QString s = v.toString();
    header_data.append(s);
    header_data.append( QLatin1String("</Data></Cell>\n") );
    }
header_data.append( QLatin1String("</Row>\n") );
header_data.append( QLatin1String("\n") );

QString end_data = QLatin1String("</Table>");
end_data.append( QLatin1String("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">") );
end_data.append( QLatin1String("<Selected/>\n") );
end_data.append( QLatin1String("<ProtectObjects>False</ProtectObjects>\n") );
end_data.append( QLatin1String("<ProtectScenarios>False</ProtectScenarios>\n") );
end_data.append( QLatin1String("</WorksheetOptions>\n") );
end_data.append( QLatin1String("<x:WorksheetOptions/>\n") );
end_data.append( QLatin1String("</Worksheet>\n") );
end_data.append( QLatin1String("</Workbook>\n") );

    while ( true == model->canFetchMore() )
    {
    model->fetchMore();
    qApp->processEvents();
    }

QString path = QFileDialog::getSaveFileName( this,
                                  tr("Select File"),
                                  last_path,
                                  tr("MS XML Spreadsheet File (*.xml);;") );

    if ( true == path.isEmpty() )
    {
    return;
    }

QFile file_(path);
    if ( file_.exists() )
    {
    QMessageBox mBox;
    mBox.setWindowTitle( tr("File exists") );
    mBox.setIcon( QMessageBox::Question );
    mBox.setText( tr("Overwrite?") );
    mBox.setStandardButtons( QMessageBox::Yes | QMessageBox::No );
        switch ( mBox.exec() )
        {
            case QMessageBox::Yes:
            break;

            default:
            return;
            break;
        }
    file_.open(QIODevice::WriteOnly);
    file_.remove();
    }
QFile file(path);
bool opened = file.open(QIODevice::ReadWrite | QIODevice::Append );
    if ( true != opened )
    {
    QMessageBox::critical( this,
                           tr("Error"),
                           tr("Could not open file.") );
    return;
    }

const QByteArray header_ba = header_data.toUtf8();
const QByteArray & header_ba_ref = header_ba;
qint64 bytes = Q_INT64_C(-1);
bytes = file.write(header_ba_ref);

    for ( int x = 0; x < model->rowCount(); ++x )
    {
    QString row_data = QLatin1String("<Row>");
    QSqlRecord record = model->record(x);
        
        if ( number_of_columns != record.count() )
        {
        QString message = tr("Internal error.");
        message.append( QLatin1String("number_of_columns != record.count()") );        
        QMessageBox::critical( this,
                               tr("Error"),
                               message );
        file.close();
        file.remove();
        return;
        }

        for ( int y = 0; y < number_of_columns; ++y )
        {
        QVariant v = record.value(y);
            if ( true != v.isNull() &&
                 true == v.isValid() )
            {
            row_data.append( QLatin1String("<Cell ss:StyleID=\"Default\" ss:Index=\"") );
            row_data.append( QString::number( (y + 1) ) );
            row_data.append( QLatin1String("\">") );

            QStringList t = m.value(y);
                if ( t.size() == 2 )
                {
                    if ( t.at(1).toUpper() == QString( QLatin1String("INT") ) )
                    {
                    row_data.append( QLatin1String("<Data ss:Type=\"Number\">") );
                    row_data.append( QString::number( v.toInt() ) );
                    row_data.append( QLatin1String("</Data>") );
                    }
                    else if ( t.at(1).toUpper() == QString( QLatin1String("UINT") ) )
                    {
                    row_data.append( QLatin1String("<Data ss:Type=\"Number\">") );
                    row_data.append( QString::number( v.toUInt() ) );
                    row_data.append( QLatin1String("</Data>") );
                    }
                    else if ( t.at(1).toUpper() == QString( QLatin1String("DOUBLE") ) )
                    {
                    row_data.append( QLatin1String("<Data ss:Type=\"Number\">") );
                    row_data.append( QString::number( v.toDouble() ) );
                    row_data.append( QLatin1String("</Data>") );
                    }
                    else if ( t.at(1).toUpper() == QString( QLatin1String("QLONGLONG") ) )
                    {
                    row_data.append( QLatin1String("<Data ss:Type=\"Number\">") );
                    row_data.append( QString::number( v.toLongLong() ) );
                    row_data.append( QLatin1String("</Data>") );
                    }
                    else if ( t.at(1).toUpper() == QString( QLatin1String("QULONGLONG") ) )
                    {
                    row_data.append( QLatin1String("<Data ss:Type=\"Number\">") );
                    row_data.append( QString::number( v.toULongLong() ) );
                    row_data.append( QLatin1String("</Data>") );
                    }
                    else if ( t.at(1).toUpper() == QString( QLatin1String("QBYTEARRAY") ) )
                    {
                    row_data.append( QLatin1String("<Data ss:Type=\"String\">") );
                    row_data.append( QLatin1String("[Binary data skipped.]") );
                    row_data.append( QLatin1String("</Data>") );
                    }
                    else
                    {
                    row_data.append( QLatin1String("<Data ss:Type=\"String\">") );
                    row_data.append( v.toString() );
                    row_data.append( QLatin1String("</Data>") );
                    }
                }
                else
                {
                QString message = tr("Internal error.");
                message.append( QLatin1String("t.size() != 2") ); 
                QMessageBox::critical( this,
                                       tr("Error"),
                                       message );
                file.close();
                file.remove();
                return;
                }
            row_data.append( QLatin1String("</Cell>\n") );
            }
        }
    row_data.append( QLatin1String("</Row>") );
    const QByteArray row_ba = row_data.toUtf8();
    const QByteArray & row_ba_ref = row_ba;
    bytes += file.write(row_ba_ref);
    }

const QByteArray end_ba = end_data.toUtf8();
const QByteArray & end_ba_ref = end_ba;
bytes += file.write(end_ba_ref);

bool success = false;
success = file.flush();
qApp->processEvents();
file.close();
qApp->processEvents();

QMessageBox::information( this,
                          tr("Information"),
                          tr("Export successful.") );
}
